package net.ecpt.clm.services;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.ofbiz.base.util.Debug;
import org.ofbiz.base.util.UtilMisc;
import org.ofbiz.base.util.UtilValidate;
import org.ofbiz.entity.Delegator;
import org.ofbiz.entity.GenericEntityException;
import org.ofbiz.entity.GenericValue;
import org.ofbiz.entity.condition.EntityCondition;
import org.ofbiz.entity.condition.EntityOperator;
import org.ofbiz.entity.util.EntityUtil;
import org.ofbiz.service.DispatchContext;
import org.ofbiz.service.ServiceUtil;
import org.ofbiz.wvtools.utils.ExcelPoiUtils;

import com.alibaba.fastjson.JSONObject;

import javolution.util.FastList;
import javolution.util.FastMap;

public class ClmInvioceXlsServices {
    public static final String module = ClmInvioceXlsServices.class.getName();

    /**
     * 导入
     */
    @SuppressWarnings("unchecked")
    public static Map<String, Object> importXls(DispatchContext dctx, Map<String, ? extends Object> context) {
	Map<String, Object> result = ServiceUtil.returnSuccess();
	Delegator delegator = dctx.getDelegator();
	List<String> messageList = new ArrayList<String>();
	List<Map<String, Object>> sheetList = (List<Map<String, Object>>) context.get("sheetList");
	try {
	    int i = 0;
	    List<GenericValue> orderList = FastList.newInstance();
	    for (Map<String, Object> row : sheetList) {
		i = i + 1;
		String relevcode = ExcelPoiUtils.getCellString(row, 0);
		String agreementCode = ExcelPoiUtils.getCellString(row, 1);
		BigDecimal collectionAmount = ExcelPoiUtils.getCellBigDecimal(row, 2);
		Timestamp billdate = ExcelPoiUtils.getCellTimestamp(row, 3);
		String corpcode = ExcelPoiUtils.getCellString(row, 4);
		String dept = ExcelPoiUtils.getCellString(row, 5);
		Timestamp busidate = ExcelPoiUtils.getCellTimestamp(row, 6);

		String tsprowid = ExcelPoiUtils.getCellString(row, 7);
		String scomment = ExcelPoiUtils.getCellString(row, 8);
		String customer = ExcelPoiUtils.getCellString(row, 9);
		String currtype = ExcelPoiUtils.getCellString(row, 10);
		BigDecimal rate = ExcelPoiUtils.getCellBigDecimal(row, 11);
		BigDecimal money = ExcelPoiUtils.getCellBigDecimal(row, 12);
		BigDecimal notaxmoney = ExcelPoiUtils.getCellBigDecimal(row, 13);
		String taxcodeid = ExcelPoiUtils.getCellString(row, 14);
		String subjcode = ExcelPoiUtils.getCellString(row, 15);
		String invproject = ExcelPoiUtils.getCellString(row, 16);

		// 判断
		if (UtilValidate.isEmpty(relevcode)) {
		    messageList.add("第" + (i + 1) + "行,收款编号不能为空。");
		    continue;
		}
		if (UtilValidate.isNotEmpty(billdate)) {
		    if (UtilValidate.isEmpty(corpcode)) {
			messageList.add("第" + (i + 1) + "行,财务组织不能为空。");
			continue;
		    }
		    if (UtilValidate.isEmpty(dept)) {
			messageList.add("第" + (i + 1) + "行,部门不能为空。");
			continue;
		    }
		    if (UtilValidate.isEmpty(busidate)) {
			messageList.add("第" + (i + 1) + "行,起算日期不能为空。");
			continue;
		    }
		}
		if (UtilValidate.isEmpty(tsprowid)) {
		    messageList.add("第" + (i + 1) + "行,收款明细编号不能为空。");
		    continue;
		}
		if (UtilValidate.isEmpty(customer)) {
		    messageList.add("第" + (i + 1) + "行,客户不能为空。");
		    continue;
		}

		if (UtilValidate.isEmpty(money)) {
		    messageList.add("第" + (i + 1) + "行,金额不能为空。");
		    continue;
		}
		if (UtilValidate.isEmpty(notaxmoney)) {
		    messageList.add("第" + (i + 1) + "行,无税金额不能为空。");
		    continue;
		}
		if (UtilValidate.isEmpty(taxcodeid)) {
		    messageList.add("第" + (i + 1) + "行,税码不能为空。");
		    continue;
		}
		if (UtilValidate.isEmpty(subjcode)) {
		    messageList.add("第" + (i + 1) + "行,贷方科目不能为空。");
		    continue;
		}

		String isSyncNc = "N";

		GenericValue entity = null;
		String id = "";
		String isOk = "N";
		if (UtilValidate.isNotEmpty(billdate) && UtilValidate.isNotEmpty(corpcode)) {
		    isOk = "Y";
		}

		String isCreate = "N";
		List<GenericValue> entityList = delegator.findByAnd("ClmInvoice",
			UtilMisc.toMap("relevcode", relevcode), null, false);

		List<GenericValue> entityList2 = delegator.findByAnd("ClmCollectionOrder",
			UtilMisc.toMap("relevcode", relevcode), null, false);
		if (UtilValidate.isEmpty(entityList2)) {
		    messageList.add("第" + (i + 1) + "行,无法在收款预估申请单中找到收款编号为 : " + relevcode + "的数据");
		    continue;
		}
		List<GenericValue> entityItemList2 = delegator.findByAnd("ClmCollectionItem",
			UtilMisc.toMap("orderId", entityList2.get(0).getString("id"), "tsprowid", tsprowid), null,
			false);
		if (UtilValidate.isEmpty(entityItemList2)) {
		    messageList.add("第" + (i + 1) + "行,无法在收款预估申请明细单中找到明细收款编号为 : " + tsprowid + "的数据");
		    continue;
		}
		if (UtilValidate.isEmpty(entityList)) {
		    id = delegator.getNextSeqId("ClmInvoice");
		    entity = delegator.makeValue("ClmInvoice", UtilMisc.toMap("id", id));
		    isCreate = "Y";
		} else {
		    entity = EntityUtil.getFirst(entityList);
		    id = EntityUtil.getFirst(entityList).getString("id");
		    isSyncNc = entity.getString("isSyncNc");
		    if (isSyncNc.equals("Y")) {
			continue;
		    }
		}
		if (isOk.equals("Y")) {
		    String agreementId = "";
		    if (UtilValidate.isNotEmpty(agreementCode)) {
			GenericValue agreement = EntityUtil.getFirst(delegator.findByAnd("ClmAgreement",
				UtilMisc.toMap("agreementCode", agreementCode), null, false));
			if (UtilValidate.isEmpty(agreement)) {
			    messageList.add("第" + (i + 1) + "行,系统合同编号不存在。");
			    continue;
			}
			agreementId = agreement.getString("id");
		    }

		    String paymentCompany = getDataDictionarySeq(delegator, "COSTPAYMENT_TYPE", corpcode);
		    if (UtilValidate.isEmpty(paymentCompany)) {
			messageList.add("第" + (i + 1) + "行,财务组织编号在系统中不存在。");
			continue;
		    }
		    dept = getDataDictionaryCode(delegator, "COSTPART_TYPE", dept);
		    entity.set("relevcode", relevcode); // 收款编号
		    String typeId = "final";
		    entity.set("dept", dept);
		    entity.set("paymentCompany", paymentCompany);
		    entity.set("isSyncNc", isSyncNc);
		    entity.set("typeId", typeId); // estimate 预估账单，final最终账单
		    entity.set("agreementId", agreementId);// 系统合同编号
		    entity.set("agreementCode", agreementCode);

		    entity.set("collectionAmount", collectionAmount); // 收款金额
		    entity.set("busidate", busidate);
		    entity.set("billdate", billdate);// 单据日期
		    entity.set("corpcode", corpcode); // 财务组织
		    if (isCreate.equals("Y")) {
			entity.create();
		    } else {
			entity.store();
		    }
		}

		GenericValue newInvoiceItem = null;
		List<GenericValue> entityItemList = delegator.findByAnd("ClmInvoiceItem",
			UtilMisc.toMap("invoiceId", id, "tsprowid", tsprowid), null, false);
		if (UtilValidate.isEmpty(entityItemList)) {
		    String newItemId = delegator.getNextSeqId("ClmInvoiceItem");
		    newInvoiceItem = delegator.makeValue("ClmInvoiceItem", UtilMisc.toMap("id", newItemId));
		    newInvoiceItem.set("id", newItemId);
		} else {
		    newInvoiceItem = EntityUtil.getFirst(entityItemList);
		}

		newInvoiceItem.set("invoiceId", id);
		newInvoiceItem.set("tsprowid", tsprowid);
		newInvoiceItem.set("scomment", scomment);
		newInvoiceItem.set("customer", customer);
		newInvoiceItem.set("currtype", currtype);
		newInvoiceItem.set("rate", rate);
		newInvoiceItem.set("money", money);
		newInvoiceItem.set("notaxmoney", notaxmoney);
		newInvoiceItem.set("taxcodeid", taxcodeid);
		newInvoiceItem.set("subjcode", subjcode);
		newInvoiceItem.set("invproject", invproject);

		orderList.add(newInvoiceItem);
	    }
	    delegator.storeAll(orderList);
	    result.put("messageList", messageList);
	} catch (GenericEntityException e) {
	    Debug.logError(e.getMessage(), module);
	}
	return result;
    }

    public static String getDataDictionarySeq(Delegator delegator, String dictionaryTypeId, String sequenceId)
	    throws GenericEntityException {
	String result = "";
	if (UtilValidate.isEmpty(sequenceId)) {
	    return result;
	}
	List<GenericValue> values = delegator.findByAnd("DataDictionary",
		UtilMisc.toMap("dictionaryTypeId", dictionaryTypeId, "sequenceId", sequenceId), null, true);
	GenericValue dataDictionary = EntityUtil.getFirst(values);
	if (UtilValidate.isNotEmpty(dataDictionary)) {
	    result = dataDictionary.getString("dictionaryCode");
	}
	return result;
    }
    public static String getDataDictionaryCode(Delegator delegator, String dictionaryTypeId, String dictionaryCode)
	    throws GenericEntityException {
	String result = "";
	if (UtilValidate.isEmpty(dictionaryCode)) {
	    return result;
	}
	List<GenericValue> values = delegator.findByAnd("DataDictionary",
		UtilMisc.toMap("dictionaryTypeId", dictionaryTypeId, "dictionaryCode", dictionaryCode), null, true);
	GenericValue dataDictionary = EntityUtil.getFirst(values);
	if (UtilValidate.isNotEmpty(dataDictionary)) {
	    result = dataDictionary.getString("dictionaryId");
	}
	return result;
    }
    
    public static String getDataDictionaryId(Delegator delegator, String dictionaryTypeId, String dictionaryId)
	    throws GenericEntityException {
	String result = "";
	if (UtilValidate.isEmpty(dictionaryId)) {
	    return result;
	}
	List<GenericValue> values = delegator.findByAnd("DataDictionary",
		UtilMisc.toMap("dictionaryTypeId", dictionaryTypeId, "dictionaryId", dictionaryId), null, true);
	GenericValue dataDictionary = EntityUtil.getFirst(values);
	if (UtilValidate.isNotEmpty(dataDictionary)) {
	    result = dataDictionary.getString("dictionaryCode");
	}
	return result;
    }
    /**
     * 导出
     */

    public static Map<String, Object> exportXls(DispatchContext dctx, Map<String, ? extends Object> context) {
	Delegator delegator = dctx.getDelegator();
	String ids = (String) context.get("ids");
	List<String> baseIdList = FastList.newInstance();
	if (UtilValidate.isNotEmpty(ids)) {
	    baseIdList = Arrays.asList(ids.split(";"));
	}
	Map<String, Object> result = ServiceUtil.returnSuccess();

	EntityCondition mainCond = null;
	List<EntityCondition> andExprs = FastList.newInstance();
	if (UtilValidate.isNotEmpty(baseIdList))
	    andExprs.add(EntityCondition.makeCondition("id", EntityOperator.IN, baseIdList));
	if (andExprs.size() > 0)
	    mainCond = EntityCondition.makeCondition(andExprs, EntityOperator.AND);

	try {
	    List<GenericValue> entityList = delegator.findList("ClmInvoice", mainCond, null, null, null, true);

	    List<Map<String, Object>> list = FastList.newInstance();
	    Map<String, Object> keyMap = FastMap.newInstance();
	    Map<String, Object> keyItemMap = FastMap.newInstance();

	    keyMap.put("0", "relevcode");
	    keyMap.put("1", "agreementCode");
	    keyMap.put("2", "collectionAmount");
	    keyMap.put("3", "billdate");
	    keyMap.put("4", "corpcode");
	    keyMap.put("5", "dept");
	    keyMap.put("6", "busidate");

	    keyItemMap.put("7", "tsprowid");
	    keyItemMap.put("8", "scomment");
	    keyItemMap.put("9", "customer");
	    keyItemMap.put("10", "currtype");
	    keyItemMap.put("11", "rate");
	    keyItemMap.put("12", "money");
	    keyItemMap.put("13", "notaxmoney");
	    keyItemMap.put("14", "taxcodeid");
	    keyItemMap.put("15", "subjcode");
	    keyItemMap.put("16", "invproject");

	    String headers = "收款编号,系统合同编号,收款金额,单据日期,财务组织,部门,起算日期,收款明细编号,摘要,客户,币种,汇率,金额,无税金额,税码,贷方科目,开票项目";
	    result.put("headers", headers);

	    for (GenericValue entity : entityList) {
		Map<String, Object> map = FastMap.newInstance();
		String relevcode = entity.getString("relevcode");
		String agreementCode = entity.getString("agreementCode");
		BigDecimal collectionAmount = entity.getBigDecimal("collectionAmount");
		Timestamp billdate = entity.getTimestamp("billdate");
		Timestamp busidate = entity.getTimestamp("busidate");
		String corpcode = entity.getString("corpcode");
		String dept =entity.getString("dept");
		dept = getDataDictionaryId(delegator, "COSTPART_TYPE", dept);

		map.put("relevcode", relevcode);
		map.put("agreementCode", agreementCode);
		map.put("collectionAmount", collectionAmount);
		map.put("billdate", billdate);
		map.put("corpcode", corpcode);
		map.put("dept", dept);
		map.put("busidate", busidate);
		List<GenericValue> entityItemList = delegator.findByAnd("ClmInvoiceItem",
			UtilMisc.toMap("invoiceId", entity.getString("id")), null, false);

		List<Map<String, Object>> itemList = FastList.newInstance();
		for (Object item : entityItemList) {
		    JSONObject itemInfo = (JSONObject) item;
		    Map<String, Object> itemMap = FastMap.newInstance();

		    String tsprowid = itemInfo.getString("tsprowid");
		    String scomment = itemInfo.getString("scomment");
		    String customer = itemInfo.getString("customer");
		    String currtype = itemInfo.getString("currtype");
		    BigDecimal rate = itemInfo.getBigDecimal("rate");
		    BigDecimal money = itemInfo.getBigDecimal("money");
		    String notaxmoney = itemInfo.getString("notaxmoney");
		    String taxcodeid = itemInfo.getString("taxcodeid");
		    String subjcode = itemInfo.getString("subjcode");
		    String invproject = itemInfo.getString("invproject");

		    if (UtilValidate.isEmpty(rate))
			rate = BigDecimal.ZERO;
		    if (UtilValidate.isEmpty(money))
			money = BigDecimal.ZERO;

		    itemMap.put("tsprowid", tsprowid);
		    itemMap.put("scomment", scomment);
		    itemMap.put("customer", customer);
		    itemMap.put("currtype", currtype);
		    itemMap.put("rate", rate);
		    itemMap.put("money", money);
		    itemMap.put("notaxmoney", notaxmoney);
		    itemMap.put("taxcodeid", taxcodeid);
		    itemMap.put("subjcode", subjcode);
		    itemMap.put("invproject", invproject);
		    itemList.add(itemMap);
		}

		map.put("itemList", itemList);
		list.add(map);
	    }
	    result.put("list", list);
	    result.put("keyMap", keyMap);
	    result.put("keyItemMap", keyItemMap);
	} catch (GenericEntityException e) {
	    Debug.logError(e.getMessage(), module);
	}
	return result;
    }

}
